from myapp.utils.connection import POOL


def locationToJson(keyword, mid=''):
    conn = POOL.connection()
    cursor = conn.cursor()
    sql = ''
    if mid == '':
        sql = 'SELECT location from {}_userspider'.format(keyword)
    else:
        sql = 'SELECT u.location from {}_userspider u ,{}_findson f WHERE f.rootknot = {} AND f.userid = u.uid'.format(
            keyword, keyword, mid)
    cursor.execute(sql)

    result = {}

    for sub_text in cursor.fetchall():
        if sub_text[0] is None:
            continue
        location = sub_text[0].split()[0]
        if location == '海外' or location == '其他':
            continue
        if location in result.keys():
            result[location] = result[location] + 1
        else:
            result[location] = 1

    final_result = {}
    ky_list = []
    for key in result:
        temp_dic = {}
        temp_dic['name'] = key
        temp_dic['value'] = result[key]
        ky_list.append(temp_dic)
    final_result['data'] = ky_list

    # conn.close()
    cursor.close()
    # jsondata=json.dumps(final_result,ensure_ascii=False)
    # f=open('testjson.json','w')
    # f.write(jsondata)
    # f.close()
    # print(final_result)
    return final_result
